package com.library.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.Connection;

public class MySQLReservationDAO implements ReservationDAO {

	@Override
	public boolean insert(Reservation reservation) {
		Connection conn = MySQLConnection.getConnection();
		if (conn != null) {
			boolean ok = true;
			String query = "INSERT INTO `reservations` (`userid`, `booktitleid`, `date`, `status`) VALUES ("
					+ reservation.getUserId()
					+ ","
					+ reservation.getBookTitleId()
					+ ","
					+ "now()"
					+ ",'"
					+ reservation.getStatus() + "')";
			try {
				Statement statement = conn.createStatement();
				statement.executeUpdate(query);
			} catch (SQLException e) {
				ok = false;
			} finally {
				MySQLConnection.closeConnection(conn);
			}
			return ok;
		} else {
			return false;
		}
	}

	@Override
	public boolean remove(int reservationId) {
		Connection conn = MySQLConnection.getConnection();
		if (conn != null) {
			boolean ok = true;
			String query = "DELETE FROM `reservations` WHERE `reservationid` = " + reservationId ;
			try {
				Statement statement = conn.createStatement();
				statement.executeUpdate(query);
			} catch (SQLException e) {
				ok = false;
			} finally {
				MySQLConnection.closeConnection(conn);
			}
			return ok;
		} else {
			return false;
		}
	}

	@Override
	public boolean update(Reservation reservation) {
		Connection conn = MySQLConnection.getConnection();
		if (conn != null) {
			boolean ok = true;
			String query = "UPDATE `reservations` SET"
					+ "`userid` =" + reservation.getUserId() + ","
					+ "`booktitleid` =" + reservation.getBookTitleId() + ","
					+ "`date` ='" + reservation.getDate() + "',"
					+ "`status` ='" + reservation.getStatus() + "'"
					+ "WHERE"
					+ "`reservationid` ='" + reservation.getReservationId() + "'";
			try {
				Statement statement = conn.createStatement();
				statement.executeUpdate(query);
			} catch (SQLException e) {
				ok = false;
			} finally {
				MySQLConnection.closeConnection(conn);
			}
			return ok;
		} else {
			return false;
		}
	}

	@Override
	public Reservation findByKey(int reservationId) {
		Connection conn = MySQLConnection.getConnection();
		Reservation reservation = null;
		if (conn!= null){
			String querry = "SELECT * FROM `reservations` WHERE reservationid = " + reservationId ;
			try {
				Statement stmt = conn.createStatement() ;
				ResultSet result = stmt.executeQuery(querry);
				if (result.next()){
					reservation = new Reservation();
					reservation.setReservationId(reservationId);
					reservation.setUserId(result.getInt("userid"));
					reservation.setBookTitleId(result.getInt("booktitleid"));
					reservation.setDate(result.getDate("date"));
					reservation.setStatus(result.getString("status"));

				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}

		}
		return reservation;
	}

	@Override
	public ArrayList<Reservation> findAll() {
		ArrayList<Reservation> reservations = null;
		Connection conn = MySQLConnection.getConnection();
		if (conn!= null){
			String querry = "SELECT * FROM `reservations` WHERE 1";
			try {
				Statement stmt = conn.createStatement() ;
				ResultSet result = stmt.executeQuery(querry);
				reservations = new ArrayList<Reservation>();
				while (result.next()){
					Reservation reservation = new Reservation();
					reservation.setReservationId(result.getInt("reservationid"));
					reservation.setUserId(result.getInt("userid"));
					reservation.setBookTitleId(result.getInt("booktitleid"));
					reservation.setDate(result.getDate("date"));
					reservation.setStatus(result.getString("status"));
					reservations.add(reservation);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}

		}
		return reservations;
	}

	@Override
	public int getNoReservationWith(String status, int bookTitleId) {
		String query = "SELECT COUNT(reservationid) FROM reservations WHERE((status = '"+status+"') AND (booktitleid='"+bookTitleId+"'))";
		Connection conn = MySQLConnection.getConnection();
		int count = -1;
		if (conn != null){
			try {
				Statement st = conn.createStatement();
				ResultSet res = st.executeQuery(query);
				while (res.next()){
					count = res.getInt(1);
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}
		}
		return count;
	}

	@Override
	public ArrayList<Reservation> findReservedBook(int userId) {
		String query = "SELECT * FROM reservations WHERE userid='"+userId+"'";
		Connection conn = MySQLConnection.getConnection();
		ArrayList<Reservation> result = new ArrayList<Reservation>();
		if (conn != null){
			try {
				Statement st = conn.createStatement();
				ResultSet res = st.executeQuery(query);
				while (res.next()){
					Reservation one = new Reservation();
					one.setBookTitleId(res.getInt("booktitleid"));
					one.setDate(res.getDate("date"));
					one.setBookAvailableDate(res.getDate("bookavailabledate"));
					one.setStatus(res.getString("status"));
					result.add(one);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}
		}
		return result;
	}

	@Override
	public ArrayList<Reservation> findByBookTitleId(int bookTitleId, String status) {
		ArrayList<Reservation> reservations = null;
		Connection conn = MySQLConnection.getConnection();
		if (conn!= null){
			String querry = "SELECT * FROM `reservations` WHERE `booktitleid` = " + bookTitleId + " AND `status` = '" + status + "'";
			try {
				Statement stmt = conn.createStatement() ;
				ResultSet result = stmt.executeQuery(querry);
				reservations = new ArrayList<Reservation>();
				while (result.next()){
					Reservation reservation = new Reservation();
					reservation.setReservationId(result.getInt("reservationid"));
					reservation.setUserId(result.getInt("userid"));
					reservation.setBookTitleId(result.getInt("booktitleid"));
					reservation.setDate(result.getDate("date"));
					reservation.setStatus(result.getString("status"));
					reservations.add(reservation);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}

		}
		return reservations;
	}

}
